<?php
/**
 * Auther: Joshua Conero
 * Date: 2017/6/22 0022 0:07
 * Email: brximl@163.com
 * Name:
 */

namespace app\common\model;


use think\Db;
use think\Model;

class Fnc1000c extends Model
{
    protected $table = 'fnc1000c';
    protected $pk = 'no';
    protected $searchOption;    // 搜索选项
    private $_whereOpt;
    private $_tagName;          // 标签项

    // 字段信息
    public $no;

    /**
     * 设置条件
     * @param object|string $where
     * @return $this
     */
    function setWhere($where){
        $where = empty($where)? false: $where;
        if(is_array($where) && isset($where['tag'])){
            $this->_tagName = $where['tag'];
            $where = false;
        }
        $this->_whereOpt = $where;
        return $this;
    }
    /**
     * 获取主键，主键设置为全局变量
     * @return mixed
     */
    public function getNoVal(){
        $no = getPkValue('pk_fnc1000c__no');
        $this->no = $no;
        return $this->no;
    }
    /**
     * @param null $option
     * @return $this
     */
    public function search($option=null){
        if($option instanceof \Closure){
            $data = $this->searchOption;
            $this->searchOption = null;
            call_user_func($option, $data);
        }
        elseif($option){
            $this->searchOption = $option;
        }else{
            $this->searchOption = null;
        }
        return $this;
    }

    /**
     * 获取一条数据记录
     * @param string $no
     * @param bool $fullListMk
     * @return array|false|\PDOStatement|string|Model
     */
    public function getOneList($no, $fullListMk=false){
        // 设置主键
        $this->no = $no;
        if($fullListMk){
            $subSql = (new Fnc1001c)->field('count(*)')->where('`src_no` = `a`.`no`')->buildSql();
            /*
            $data = $this->db()
                ->alias('a')
                ->join(['fnc0020c' => 'b'],'a.master_id=b.listid')
                ->join(['fnc0010c' => 'c'],'a.tag_id=c.listid','left')
                ->join(['fnc2000c' => 'd'],'a.src_plan_no=d.plan_no','left')
                ->join(['fnc0030c' => 'e'],'a.subject_id=e.listid','left')
                ->field([
                    'a.*,b.name as master, c.tag as tagid_desc,d.plan as splanno_desc, e.subject',
                    $subSql=>'detail_ctt'
                ])
                ->where('a.no',$no)
                ->find();
            */
            $wh = $data = $this->db()
                ->alias('a')
                ->join(['fnc0020c' => 'b'],'a.master_id=b.listid')
                ->join(['fnc0010c' => 'c'],'a.tag_id=c.listid','left')
                ->join(['fnc2000c' => 'd'],'a.src_plan_no=d.plan_no','left')
                ->join(['fnc0030c' => 'e'],'a.subject_id=e.listid','left')
                ->field([
                    'a.*,b.name as master, c.tag as tagid_desc,d.plan as splanno_desc, e.subject',
                    $subSql=>'detail_ctt'
                ])
                ->where('a.no',$no);
            $this->search(function($dd) use(&$wh){
                if($dd){}
            });
            $data = $wh->find();
        }
        else{
            /*
            $data = $this->db()
                ->alias('a')
                ->join(['fnc0020c' => 'b'],'a.master_id=b.listid')
                ->join(['fnc0010c' => 'c'],'a.tag_id=c.listid','left')
                ->join(['fnc2000c' => 'd'],'a.src_plan_no=d.plan_no','left')
                ->field('a.*,b.name as master, c.tag as tagid_desc,d.plan as splanno_desc')
                ->where('a.no',$no)
                ->find();
            */
            $wh = $this->db()
                ->alias('a')
                ->join(['fnc0020c' => 'b'],'a.master_id=b.listid')
                ->join(['fnc0010c' => 'c'],'a.tag_id=c.listid','left')
                ->join(['fnc2000c' => 'd'],'a.src_plan_no=d.plan_no','left')
                ->field('a.*,b.name as master, c.tag as tagid_desc,d.plan as splanno_desc')
                ->where('a.no',$no)
                ;
            $this->search(function($dd) use(&$wh){
                if($dd){}
            });
            $data = $wh->find();
        }
        if($data) $data = $data->toArray();
        return $data;
    }
    /**
     * 获取数据记录
     * @param int $page
     * @param int $num
     * @return ($data,$count)
     */
    public function getFinanceSets($page=1,$num=20){
        $uid = getUserInfo('uid');
        if(empty($uid)) return [null,0];

        // where 条件组织
        $whereData = false;
        if($this->_whereOpt && is_array($this->_whereOpt)){
            $whereData = [];
            foreach ($this->_whereOpt as $key => $value){
                $whereData[] = ['a.'.$key, 'like', '%'.$value.'%'];
            }
        }

        $subSql = (new Fnc1001c)
            ->field('count(*)')
            ->where('`src_no` = `a`.`no`')
            ->where($whereData)
            ->buildSql();

        $tagSubSql = '';
        if($this->_tagName){
            $tagSubSql = (new Fnc1000c())
                ->alias('i10')
                ->field('i10.no')
                ->join(['fnc1002c' => 'i12'], 'i10.no=i12.src_no')
                ->join(['fnc0010c' => 'i00'], 'i12.tag_id=i00.listid')
                ->where('i00.tag', 'like', '%'.$this->_tagName.'%')
                ->buildSql(true)
                ;
        }

        /*
        $data = $this->db()
            ->alias('a')
			->join(['fnc0020c' => 'b'],'a.master_id=b.listid')
            ->join(['fnc0010c' => 'c'],'a.tag_id=c.listid','left')
            ->join(['fnc2000c' => 'd'],'a.src_plan_no=d.plan_no','left')
			->join(['fnc0030c' => 'e'],'a.subject_id=e.listid','left')
			->join(['fnc0020c' => 'f'],'a.slave_id=f.listid','left')
            ->field(['a.*,b.name as master, c.tag as tagid_desc,d.plan as splanno_desc,e.subject',$subSql=>'detail_ctt','DATE_FORMAT(date,\'%w\') as week','ifnull(f.name, a.slave)'=>'slave'])
            ->where('a.uid',$uid)
            ->order('a.date desc,a.mtime desc')
            ->page($page,$num)
            ->select()
        ;
        */
        $wh = $data = $this->db()
            ->alias('a')
            ->join(['fnc0020c' => 'b'],'a.master_id=b.listid')
            ->join(['fnc0010c' => 'c'],'a.tag_id=c.listid','left')
            ->join(['fnc2000c' => 'd'],'a.src_plan_no=d.plan_no','left')
            ->join(['fnc0030c' => 'e'],'a.subject_id=e.listid','left')
            ->join(['fnc0020c' => 'f'],'a.slave_id=f.listid','left')
            ->field(['a.*,b.name as master, c.tag as tagid_desc,d.plan as splanno_desc,e.subject',$subSql=>'detail_ctt','DATE_FORMAT(date,\'%w\') as week','ifnull(f.name, a.slave)'=>'slave'])
            ->where('a.uid',$uid)
            ->where($whereData)
            ;
        if($this->_tagName){
            $wh = $wh->join([$tagSubSql => 'vt'], 'a.no=vt.no');
        }
        $this->search(function($dd) use(&$wh){
            if($dd){
                //debugOut([$dd, '8']);
                $colMap = [
                    'date' => 'a.date',
                    'money' => 'a.money',
                    'master' => 'b.name',
                    'subject' => 'e.subject',
                    'name' => 'a.name',
                    'descrip' => 'a.descrip',
                    'mtime' => 'a.mtime'
                ];
                foreach($dd as $v){
                    $sCol = $v['sCol'];
                    $value = $v['value'];
                    $equal = $v['equal'];
                    if('like' == $equal){   // 包含
                        $col = isset($colMap[$sCol])? $colMap[$sCol]: null;
                        if($col) $wh->where($col, 'like', "%$value%");
                    }
                    elseif ('begin' == $equal){ // 以什么开头
                        $col = isset($colMap[$sCol])? $colMap[$sCol]: null;
                        if($col) $wh->where($col, 'like', "$value%");
                    }
                    elseif ('end' == $equal){   // 以什么结尾
                        $col = isset($colMap[$sCol])? $colMap[$sCol]: null;
                        if($col) $wh->where($col, 'like', "%$value");
                    }
                    elseif ('=' == $equal){ // 等于
                        $col = isset($colMap[$sCol])? $colMap[$sCol]: null;
                        if($col) $wh->where($col, $value);
                    }
                }
            }
        });
        $data = $wh->order('a.date desc,a.mtime desc')
            ->page($page,$num)
            ->select()
            ;
        $count = $wh->count();
        return [$data,$count];
    }

    /**
     * 财务数据汇总(流水账)
     * @param int $page
     * @param int $num
     * @param string $type
     * @param string|null $filter_type 筛选类型， 资金流量(all)、纯资金量(part)
     * @return array
     */
    public function getFinanceSetsGroup($page=1,$num=20, $type='DAY', $filter_type='part'){
        $uid = getUserInfo('uid');
        $count = 0;
        if(empty($uid)) return [null,$count];
        $bind = ['uid'=>$uid];

        // where 条件组织
        $t0WhBindStr = '';
        $aWhBindStr = '';
        if($this->_whereOpt && is_array($this->_whereOpt)){
            $t0WhBindStr = [];
            $aWhBindStr = [];
            foreach ($this->_whereOpt as $key => $value){
                $key = 'vw_' . $key;
                $t0WhBindStr[] = 't0.'. $key . ' like : '.$key;
                $aWhBindStr[] = 'a.'. $key . ' like : '.$key;
                $bind[$key] = '%'.$value.'%';
            }
            if(!empty($t0WhBindStr)){
                $t0WhBindStr = ' AND ' . implode(' AND ', $t0WhBindStr);
                $aWhBindStr = ' AND ' . implode(' AND ', $aWhBindStr);
            }
        }
        if(is_array($t0WhBindStr)){
            $t0WhBindStr = '';
        }
        $type = strtoupper($type);
        $limit = ' limit '.(($page-1)*$num).','.$num;
        $cttRs = null;
        $table = '
        (
            select 
                if(t0.type = \'IN\', t0.money, 0-t0.money) as money, 
                t0.date, 
                t0.uid 
            from fnc1000c t0
            join fnc0020c t1 on t0.master_id=t1.listid
            left join fnc0020c t2 on t0.slave_id=t2.listid
            where 
                t0.uid=:uid'.$t0WhBindStr.' and 
                t2.type is null 
                or 
                ( t1.type <> t2.type 
                  and t2.type<>\'M0\')                
        )
        ';
        switch ($type){
            // year
            case 'YEAR':
                if('part' == $filter_type){
                    $sql = '
                        select 
                          date_format(a.`date`, \'%Y\') as `date`, sum(a.`money`) as `money`, count(*) as `num` 
                          from '.$table.' a
                          group by date_format(a.`date`, \'%Y\') order by a.`date` desc
                    '.$limit;
                    $sqlCount = '
                            select 
                              count(*) as `ctt`
                              from (
                                '.$sql.'
                              ) bb
                    ';
                }
                else{
                    $sql = '
                        select 
                          date_format(a.`date`, \'%Y\') as `date`, sum(a.`money`) as `money`, count(*) as `num` 
                          from fnc1000c a
                          where a.`uid`=:uid'.$aWhBindStr.'
                          group by date_format(a.`date`, \'%Y\') order by a.`date` desc
                    '.$limit;
                        $sqlCount = '
                            select 
                              count(*) as `ctt`
                              from (
                                select
                                  date_format(a.`date`, \'%Y\') as `date`
                                  from fnc1000c a
                                  where a.`uid`=:uid
                                  group by date_format(a.`date`, \'%Y\')
                              ) a
                    ';
                }
                break;
            // month
            case 'MONTH':
                if('part' == $filter_type){
                    $sql = '
                        select 
                          date_format(a.`date`, \'%Y%m\') as `date`, sum(a.`money`) as `money`, count(*) as `num` 
                          from '.$table.' a
                          group by date_format(a.`date`, \'%Y-%m\') order by a.`date` desc
                    '.$limit;
                    $sqlCount = '
                            select 
                              count(*) as `ctt`
                              from (
                                '.$sql.'
                              ) bb
                    ';
                }
                else{
                    $sql = '
                        select 
                          date_format(a.`date`, \'%Y%m\') as `date`, sum(a.`money`) as `money`, count(*) as `num` 
                          from fnc1000c a
                          where a.`uid`=:uid'.$aWhBindStr.'
                          group by date_format(a.`date`, \'%Y-%m\') order by a.`date` desc
                    '.$limit;
                        $sqlCount = '
                            select 
                              count(*) as `ctt`
                              from (
                                select
                                  date_format(a.`date`, \'%Y%m\') as `date`
                                  from fnc1000c a
                                  where a.`uid`=:uid
                                  group by date_format(a.`date`, \'%Y-%m\')
                              ) a
                    ';
                }
                break;
            default:
                if('part' == $filter_type){
                    $sql = '
                        select 
                          a.`date`, sum(a.`money`) as `money`, count(*) as `num`,DATE_FORMAT(date,\'%w\') as week 
                          from '.$table.' a
                          group by a.`date` order by a.`date` desc
                    '.$limit;
                    $sqlCount = '
                            select 
                              count(*) as `ctt`
                              from (
                                '.$sql.'
                              ) bb
                    ';
                }
                else{
                    $sql = '
                        select 
                          a.`date`, sum(a.`money`) as `money`, count(*) as `num`, date_format(a.`date`, \'%w\') as week                          from fnc1000c a
                          where a.`uid`=:uid'.$aWhBindStr.'
                          group by a.`date` order by a.`date` desc
                    '.$limit;
                        $sqlCount = '
                            select 
                              count(*) as `ctt`
                              from (
                                select 
                                      a.`date`
                                      from fnc1000c a
                                      where a.`uid`=:uid
                                      group by a.`date`
                              ) a
                    ';
                }
        }
        debugOut($sql);
        $cttRs = Db::query($sqlCount, $bind);
        if($cttRs){
            $count = $cttRs[0]['ctt'];
        }
        $data = Db::query($sql, $bind);
        return [$data,$count];
    }

    /**
     * 获取项目的 tag 标签
     * @param string|null $no 主键，不设置为当前查询到的主键
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getTags($no=null){
        $no = $no ?? $this->no;
        $tags = [];
        $data = Db::table('fnc1002c')
            ->alias('f12c')
            ->join(['fnc0010c' => 'f01c'], 'f12c.tag_id=f01c.listid')
            ->field('f12c.tag_id, f01c.tag')
            ->where('f12c.src_no', $no)
            ->select()
        ;
        foreach ($data as $vv){
            $tags[$vv['tag_id']] = $vv['tag'];
        }
        return $tags;
    }

    /**
     * 标签保存/新增与更新
     * @param array $data [{id, name}]
     * @param null|string $no
     * @param null|string $uid
     * @return bool
     * @throws \think\Exception
     * @throws \think\exception\PDOException
     */
    public function tagSave($data, $no=null, $uid=null){
        $no = $no ?? $this->no;
        if($no){
            // 删除已经存在的标签，再新增
            Db::table('fnc1002c')->where(['src_no' => $no])->delete();
            $save = [];
            $uid = $uid ?? getUserInfo('uid');
            $data = is_array($data)? $data : [];

            foreach ($data as $vv){
                $tagName = $vv['name'] ?? false;
                if(!isset($vv['id'])){
                    // 没有设置 id 时，自动去数据库表中查找避免人为重复的id生成
                    // 1. 首先查找个人下的标签
                    // 2. 其次找所用公共的
                    $findTagId = false;
                    if($uid && $tagName){
                        $tagId = Db::table('fnc0010c')
                            ->where(['uid' => $uid, 'tag' => $tagName])
                            ->order('mtime desc')
                            ->value('listid');
                        if($tagId){
                            $vv['id'] = $tagId;
                            $findTagId = true;
                        }
                    }

                    // method 2
                    if(!$findTagId && $tagName){
                        $tagId = Db::table('fnc0010c')
                            ->where(['tag' => $tagName, 'private_mk' => 'N'])
                            ->order('mtime desc')
                            ->value('listid');
                        if($tagId){
                            $vv['id'] = $tagId;
                        }
                    }
                }
                // 存在的标签
                if(isset($vv['id'])){
                    $save[] = [
                        'tag_id' => $vv['id'],
                        'src_no' => $no
                    ];
                }else if($uid && $tagName){
                    // 不存在标签，需要存在登录用户
                    $tagModel = new Fnc0010c();
                    if($tagModel->save([
                        'tag'       => $vv['name'],
                        'private_mk'=> 'Y',
                        'uid'       => $uid
                    ])){
                        $save[] = [
                            'tag_id' => $tagModel->listid,
                            'src_no' => $no
                        ];
                    }
                }
            }
            Db::table('fnc1002c')->insertAll($save);
        }
        return false;
    }

    /**
     * 标签删除
     * @param null|string $no
     * @throws \think\Exception
     * @throws \think\exception\PDOException
     */
    public function removeTags($no = null){
        $no = $no ?? $this->no;
        $map = ['src_no' => $no];
        sysRecycle('fnc1002c', $map,'auto');
        Db::table('fnc1002c')->where($map)->delete();
    }
}